package xmlcollabserver;

import java.sql.Statement;
import java.sql.PreparedStatement;
import java.math.BigInteger;
import java.security.MessageDigest;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Sasa
 */
public class DataBase {

    private Connection con;

/*
    public static void main(String[] args) throws Exception {
        DataBase db = new DataBase();
        db.connect("localhost:3306", "collab", "root", "");
       // Integer lastId = db.newClient("admin", "admin");
        Client client = db.getClient("admin", "admin");
        //System.out.println("ID:"+ client.id + " " +client.username);

        List<Client> clients = db.getAllClients();

        for(Integer i=0; i< clients.size(); i++) {
            System.out.println("ID:"+ clients.get(i).id + " " +clients.get(i).username);
        }
    }
*/
    public DataBase() throws Exception {
        //System.out.println("aaa");
        connect("localhost:3306", "collab", "root", "");
    }

    public void connect(String host, String name, String user, String pass) throws Exception {
       Class.forName("com.mysql.jdbc.Driver");
       this.con = DriverManager.getConnection("jdbc:mysql://"+host+"/"+name, user, pass);
       System.out.println("Connected to database: "+ host);
    }

    /**
     * Create new client row in db
     *
     * @param username
     * @param password
     * @return client id
     * @throws Exception
     */
    public Integer newClient(String username, String password) throws Exception {
        PreparedStatement ps = this.con.prepareStatement("INSERT clients (Username, Password) VALUES(?, ?)");

        ps.setString(1, username);
        ps.setString(2, hashPassword(password));
        ps.executeUpdate();

        ResultSet rs = ps.getGeneratedKeys();
        rs.next();
        Integer lastId = rs.getInt(1);
        
        rs.close();
        ps.close();

        return lastId;
    }

    /**
     * Get one client by username and password
     * 
     * @param username
     * @param password
     * @return
     * @throws Exception
     */
    public Client getClient(String username, String password) throws Exception {
        Client client = new Client();
        PreparedStatement ps = this.con.prepareStatement("SELECT * FROM clients WHERE Username = ? AND Password = ? LIMIT 1");

        ps.setString(1, username);
        ps.setString(2, hashPassword(password));
       
        ResultSet rs = ps.executeQuery();
        if(rs.next()) {
            
            client.id = rs.getInt("Id");
            client.username = rs.getString("Username");
            client.password = rs.getString("Password");
        }
        return client;
    }

    /**
     * Get all clients from db in list
     * @return list
     * @throws Exception
     */
    public List<Client> getAllClients() throws Exception {
        
        Statement st = this.con.createStatement();

        ResultSet rs = st.executeQuery("SELECT * FROM clients");

        List<Client> clients = new ArrayList<Client>();
        while(rs.next()) {
            Client client = new Client();
            client.id = rs.getInt("Id");
            client.username = rs.getString("Username");
            client.password = rs.getString("Password");

            clients.add(client);
        }
        return clients;
    }

    public List<Task> getAllTasks(Integer memberId) throws Exception {

        PreparedStatement ps = this.con.prepareStatement("SELECT * FROM tasks WHERE memberId = ?");
        ps.setInt(1, memberId);
        ResultSet rs = ps.executeQuery();
        List<Task> tasks = new ArrayList<Task>();
        while(rs.next()) {
            Task task = new Task();
            task.id = rs.getInt("Id");
            task.memberId = rs.getInt("MemberId");
            task.due = rs.getInt("Due");
            task.priority = rs.getInt("Priority");
            task.assigment = rs.getString("Assigment");

            tasks.add(task);
        }
        return tasks;
    }


    /**
     * Create md5 hash string
     * @param password
     * @return
     * @throws Exception
     */
    public static String hashPassword(String password) throws Exception {
        String hashword = null;
        MessageDigest md5 = MessageDigest.getInstance("MD5");
        md5.update(password.getBytes());
        BigInteger hash = new BigInteger(1, md5.digest());
        hashword = hash.toString(16);
        return hashword;
    }

}

class Client {
    Integer id;
    String username;
    String password;
}
class Task {
    Integer id;
    Integer memberId;
    Integer due;
    Integer priority;
    String assigment;
}